Fritz: All Fritz

home *** CD-ROM | disk | FTP | other *** search

/ Fritz: All Fritz / All Fritz.zip / All Fritz / FILES / SPREEETS / TC511.LZH / TC511.DOC < prev next >

Wrap

Text File | 1986-06-22 | 29KB | 528 lines

Turbo Calc v5.11 Copyright (c) 1985, 1986: by P & M Software Co. Turbo Calc is "User Supported" software. If you like the program, use it, and want to see it supported and improved in the future, a contribution of $20 would be appreciated. Please copy and share this program with your friends, even if you are unable to make a contribution. Send Contributions to: P & M Software Co. 9350 Country Creek #30 Houston, Tx 77036 Turbo Calc is intended to be powerfull, yet easy to use. It supports a medium sized spreadsheet, 512 rows by 64 columns. And supports many math and financial functions. Hopefully the user can begin using the program without reading a lot of documentation, but some information is necessary before one can begin. The program requires a minimum of 256k and can use as much as 640k. INSTALLATION ------------ Turbo Calc is overlayed, the .000 file that came with the program is the overlay file. It must be present in the default directory when the program is started, and it may not be removed from the system during execution of the program. You may change directories after starting Turbo Calc, because Turbo Calc will automatically find its way back to the original directory for the overlays. This is true for the the config file, .CNF, also. The config file holds the information about your printer setup. DESCRIPTION ----------- First, let us examine the layout of the worksheet. On screen, there will be displayed 7 columns by 21 rows. The user may scroll both vertically and horizontally. The vertical scroll is accomplished by depressing the PgUp or PgDn keys. This will scroll 20 rows up or down, a single line scroll is achieved by moving the cursor off the top or bottom of the screen, with the up or down arrow keys. The horizontal scroll is achieved by depressing the Ctrl key and the left or right arrow keys simultaneously. On the top and bottom lines of the screen there appears some status information: the current cell, the amount of memory available, the auto-calc indicator, the current marked range, the current drive and directory, the CAPS and NUM lock indicators. On the second line of the screen will appear messages and the numeric or formula content of the current cell. In general, the user may move the cursor to whatever cell he desires, and enter the text, number or formula that needs to be in that location. If entering text, it can overwrite cells to the right, but may not be longer than 80 characters. When you begin to enter something into a cell, the first keystroke is VERY important. It will determine the type of item stored in the cell: formulas begin with the '(' character, numbers with 0 thru 9, '+' or '-', text with any other character. When making the first keystroke of a cell, some keys have different meanings, than on succeeding keystrokes. For example, the left and right arrow keys will move the cursor around the worksheet, but during the entry of data into a cell, these keys will act to move the cursor within the cell, to edit the data. Other keys that behave differently, are the Home, End and Esc keys. The Home and End keys move the cursor to the beginning or the end of the worksheet, but during the entry of data, they move to the beginning or end of the current cell. Also during data entry, the Esc key will act to finish the entry and move to the cell below, so that a column of numbers may be entered with the Num Lock turned on. If you depress the Esc key, while not entering data, a list of commands will appear, any of the displayed commands may be executed by pressing the indicated letter. For experienced users, the commands may be entered directly, without using the Esc key, by pressing the Alt key and the command letter together. Following are two lists. The first is a list the the cursor control and editing keys. The second is a list of the command letters. Action Action Key Name During Data Entry Otherwise ------------------------------------------------------------- Up Arrow | Finish entry and | Move to cell above. | move to cell above. | ------------------------------------------------------------- Down Arrow | Finish entry and | Move to cell below. | move to cell below. | ------------------------------------------------------------- Left Arrow | Move 1 character to | Move to cell on the | the left. | left ------------------------------------------------------------- Right Arrow | Move 1 character to | Move to cell on the | the right. | right. ------------------------------------------------------------- Home | Move to the start of | Move to cell at the | the cell. | start of worksheet. ------------------------------------------------------------- End | Move to the end of | Move to cell at the | the cell. | end of worksheet. ------------------------------------------------------------- Ctrl + End | Erase contents of | Erase current cell | cell from cursor to | entirely. | end of cell. | ------------------------------------------------------------- Ctrl + L. Arrow | Finish entry and | Scroll to the left. | scroll to the left. | ------------------------------------------------------------- Ctrl + R. Arrow | Finish entry and | Scroll to the right. | scroll to the right. | ------------------------------------------------------------- Tab | Finish entry and | Move to cell on the | move to cell right. | right. ------------------------------------------------------------- Shift + Tab | Finish entry and | Move to cell on the | move to cell left. | left. ------------------------------------------------------------- Ins | Toggle Insert Mode. | Toggle Insert Mode. | Fat cursor = Insert. | Fat cursor = Insert. ------------------------------------------------------------- Del | Delete character at | Delete cursor at | cursor. | cursor. ------------------------------------------------------------- Backspace | Delete character to | No action. | left of cursor. | ------------------------------------------------------------- Enter | Finish entry. Stay | No action. | in current cell. | ------------------------------------------------------------- Esc | Finish entry and | Displays the command | move to cell below. | menu. | Nice for using NUM | | Lock mode with a | | column of numbers! | ------------------------------------------------------------- Command Table: With Alt key, or preceeded by Esc. Esc brings up the command list. ------------------------------------------------------------- X Exit to DOS. ------------------------------------------------------------- Q Remove command list and return to worksheet. ------------------------------------------------------------- L Produce a directory list. ------------------------------------------------------------- N Change to new directory and/or disk drive. ------------------------------------------------------------- R Read worksheet from disk into memory. ------------------------------------------------------------- S Store worksheet from memory onto disk. Both the R and S commands assume the .SSF extension if the user doesn't specify one. If no extension is desired end the filename with a '.' and no ext- ension will be used. ------------------------------------------------------------- E Erase the current worksheet from memory. ------------------------------------------------------------- G Perform the calculations specified by user formula. Note: the worksheet is not automatically recalculated after one inserts/deletes a row or column, one should use the G command to do this. ------------------------------------------------------------- P Print the worksheet to disk file or printer. ------------------------------------------------------------- F Change cell format. Each cell may have a different format. The default is leading sign, no comma and no currency symbol. You may choose 1 of 6 different formats: financial, percent, scientific, hidden, date, and the default. If the date format is chosen, the cell is treated as if it contained a Julian date and then it is converted to one of three sub-formats: DD-MMM-YY, DD-MMM or MMM-YY. You will be given you choice of these 3 date formats, if you select the date type. If the financial format is chosen, you will be given the choice of 4 sub-formats: A. $1,000- .... Dollar sign and comma insertion. B. 1,000- .... Comma insertion. C. (1,000) .... Dollar sign and comma insertion, parens for negative. D. $(1,000) .... Comma insertion, parens for negative. ------------------------------------------------------------- W The width of each column defaults to 10, but it can be changed with this command to any value between 0 and 30. After setting the width, the user is asked if the column should be given the "blank zero" attribute. Note: If a column width of 0 is chosen, then this has the same effect as if the whole column had the "hidden" format. Anything in a 0 width column or with the "hidden" format, will appear on the status line at the top of the screen, but will not appear in the worksheet. ------------------------------------------------------------- B Mark the start of the range at the current cell. ------------------------------------------------------------- K Mark the end of the range at the current cell. ------------------------------------------------------------- H The user may specify that columns are to be held on the left of the screen and not horizontally scrolled. 4 maximum. ------------------------------------------------------------- I Insert Row or Column. Formula will be adjusted to reflect their new locations. Absolute addresses may be used, by placing a '0' before the row. For example 'C5' is a relative cell address and would be adjusted if needed, but 'C05' is an absolute cell address and would not be adjusted. Recalculation does not automatically take place after execution of this command. ------------------------------------------------------------- D Delete Row or Column. Formula will be adjusted as given above for 'I'. If the range option is selected, then the rows or columns that fall within the specified range will be deleted. Note: only rows or columns are deleted, not both at the same time. Recalculation does not automatically take place after execution of this command. ------------------------------------------------------------- J Jump directly to a cell. Avoid scrolling when you know where to go. ------------------------------------------------------------- M Move the indicated range to the current location. The user is given the opportunity to alter the range specification. Formula are adjusted as above for 'I'. ------------------------------------------------------------- C Copy the indicated range to the current location. The user is given the opportunity to alter the range specification. Formula are adjusted as above for 'I'. ------------------------------------------------------------- O Sort the indicated range into ascending order. The sort key is the first column of the range and the rows within the range are arranged into ascending sequence based on the textual content of the key column (the first one in the indicated range). ------------------------------------------------------------- A Toggle auto-calculate. If many formulas are in the worksheet, it may save time to turn auto-calculate off, and manually calculate with the 'G' command. ------------------------------------------------------------- Y Import a DIF(tm) format file. DIF(tm) stands for Data Interchange Format and is commonly supported by many programs including 1-2-3(tm). The DIF(tm) supports only the storage of the data, so the format control information and the formulas are not found in the in the DIF(tm) file. The extension ".DIF" is used automatically. ------------------------------------------------------------- Z Export a DIF(tm) format file. Please read the para- graph about for the 'Y' command. The output file will contain data only, no formulas or format control information. Also, the DIF(tm) format is not as efficient as the Turbo CALC .SSF format, so the exported file will probably be about 10% to 300% larger than the original file. ------------------------------------------------------------- Note: DIF is a trademark of Software Arts Products Corp. 1-2-3 is a trademark of Lotus Development Corp. Now some discussion about selected topics: 1. Formulas. Formulas are composed of operational symbols, cell addresses, constants, and functions. Each formula is enclosed within parenthesis, like this: (A1+C2+F4). This formula would take the content of the 3 cells and add them together, depositing the result in the cell where the formula is located. The following function are available: Name Description Usage ---- ----------- -------- ABS Absolute value. ABS(p) SQRT Square root. SQRT(p) SQR Square. SQR(p) MOD The remainder of p1 divided by p2. MOD(p1,p2) INT Returns the integer portion of p. INT(p) PI 3.1415926536 PI SIN Sine of angle in radians. SIN(p) ASIN Arc sine. ASIN(p) COS Cosine of angle in radians. COS(p) ACOS Arc cosine. ACOS(p) TAN Tangent of angle in radians. TAN(p) ATAN Arc Tangent. ATAN(p) LN Natural Log. LN(p) LOG Log base 10. LOG(p) E 2.7182818285 E EXP E raised to the p power. EXP(p) NPV Net present value of a series NPV(i,r) of future cash flows. PMT Mortgage payment per period. PMT(pa,i,n) PV Present value of ordinary annuity. PV(pp,i,n) FV Future value of ordinary annuity. FV(pp,i,n) RATE Periodic rate needed to produce RATE(fv,pv,n) a future value. TERM The number of payment periods required TERM(pp,i,fv) for an annuity to grow to a future value. CTERM The number of compounding periods CTERM(i,fv,pv) required for a present value to future value. IRR Internal Rate of Return. IRR(g,r) AVG The average. AVG(r) COUNT The number of items in range. COUNT(r) MIN The smallest number in range. MIN(r) MAX The biggest number in range. MAX(r) VAR The population variance. VAR(r) STD The population standard deviation. STD(r) SYD The depreciation for the specified SYD(cst,sal,lif,per) period, using the "Sum Of The Years Digits" method. SLN The "Straight line" depreciation for SLN(cst,sal,lif) 1 period. DDB The depreciation for the specified DDB(cst,sal,lif,per) period, using the "Double Declining Balance" method. IF Choose between two values. IF(t,p1,p2) p1 is returned if t is true, p2 is returned if t is false. NOT Reverse true and false values. NOT(t) TRUE Returns the value for true. TRUE FALSE Returns the value for false. FALSE ERR Returns the value for ERR. ERROR ISERR Returns the value for true if the ISERR(p) parameter has the ERR value, otherwise returns the value for false. NA Returns the value for NA. NA ISNA Returns the value for true if the ISNA(p) parameter has the NA value, otherwise returns the value for false. DATE Returns the Julian date from the DATE(y,m,d) calendar date given as a parameter. The year maybe between 1800 and 2100 and the date is measured in days from December 31, 1899. TODAY Returns the Julian date for today. TODAY Taken from the DOS clock, so if you have set it wrong, then this will be wrong. DAY Returns the day of the month, DAY(p) given the Julian date. MONTH Returns the month of the year, MONTH(p) given the Julian date. YEAR Returns the year, given the YEAR(p) Julian date. HLOOKUP Table lookups find the largest table HLOOKUP(trgt,tbl,ofs) entry not greater than the target value. The "HLOOKUP" function defines a horizontal table. VLOOKUP The "VLOOKUP" function defines a VLOOKUP(trgt,tbl,ofs) vertical table. Note: There are many examples in the sample spreadsheet that accompanies the program. Note: There is no SUM function, instead use the colon, ":", as discussed below under symbols. Where: p,p1,p2 ... Constants, cell addresses or algebraic expressions. i ......... Interest rate, may be any of the items listed for "p" above. Note: all interest rates are stated in their fractional format, i.e. "10% = 0.1". Note: this is an interest rate per period and not per annum, however if the period in use is a year, then it is per annum. If a month is the period in use, then conversion from the yearly rate must be done by division by 12. r ......... A range of cell addresses, i.e. "D4..H21". pa ........ Principle amount, may be any of the items listed for "p" above. n ......... Number of periods, may be any of the items listed for "p" above. pp ........ Payment per period, may be any of the items listed for "p" above. g ......... A guess at the interest rate, usually between 0.0 and 1.0. pv ........ A present value, may be any of the items listed for "p" above. fv ........ A future value, may be any of the items listed for "p" above. t ......... A logical expression whose result is 0.0 for false and non-zero for true. y,m,d ..... May be any value listed for "p" above. They are the symbols for year, month and day. trgt ...... May be any value listed for "p" above. It is the value which is the target of the lookup. tbl ....... A range of cell addresses that define the values to be searched to find the match for the target in a lookup. ofs ....... May be any value listed for "p" above. It is the value added to the matching entry's coordinates to find the answer in a lookup table. Usually is a 1. cst ....... The cost of an asset. sal ....... The salvage value of an asset. lif ....... The life expectancy of an asset. per ....... The period for which depreciation is to be calculated. Formulas may be quite complicated, an example is: (A1+SIN(PI/2*(B2-C1))) The only limitations imposed are those of available memory and the fact that no formula may be longer than 80 characters. The financial and trig function are not recusive, for example don't do this: (IRR(IRR(.2,D4..D20),E2..M2)), it won't work very well, if at all. If any of the formulas cannot calculate the answer, for example if you ask for the SQRT of a negative number, then the result will be ERROR and you will see the word "ERR" in the result cell. Finally, a formula may contain the following symbols: ^ Raise to the power. * Multiplication / Division + Addition - Subtraction : Range summation | Logical OR & Logical AND > Greater than test < Less than test = Equal test >= Greater than or equal test <= Less than or equal test These are probably familiar, with the exception of the ":" for range summation. Let's say that you want to sum a column of numbers, beginning at D1 and ending at D20, then this expression would sum the column, (D1:D20). 2. Overwritten cells. Turbo Calc will allow you to make text entries of any length up to a maximum of 80 characters, numeric entries may be up to 11 characters. Any text entry that exceeeds the cell width will automatically lock the adjacent cell on the right. The lock will be relased when the length of the entry shrinks. 3. The Range and the commands that use it. The range is shown on the bottom left of the worksheet screen. It is of the form: cell address..cell address. If you have not set the range, it defaults to the beginning and ending cell of the worksheet. There are two methods of setting the range: 1st, you can make the beginning and ending cells with the B and K commands, 2nd, whenever one of the commands that use the range is invoked, you have the opportunity of changing the range by entering the new range. For example the Copy command will give you a menu with 3 options: Range, Copy and eXit. If you select the Range option, you will be allowed to enter a new range, in the same format as displayed at the bottom of the screen. Here are the commands affected by the range: Copy, Move and Print. 4. Printer Setup. If you select the Print command, one of the options presented on the Print menu is "Setup". If you select the Setup option you will be able to control the margins and dimensions of the printed report. Also, you will be given the option called Control. The Control option allows you to specify printer setup control codes and printer exit control codes. These control codes default to Form Feed characters, but you may specify whatever necessary. The convention for specifying these codes is to use the decimal numbers representing the control codes separated by commas. You can also put plain text in the control strings, if you surround the text with " marks. For example: "This is a control string",12,12 If this was the setup control string, when the print began the program would send the following to the printer: 'This is a control string' FF FF. Of course, the quotes would not be sent, and the FF is just a symbolic way to represent the form feed character. With this method, you may program your printer in whatever way you wish. There are 2 dot commands for the printer. If you put .PAGE in column A of any row, it will cause a page break to occur at that place. The remainder of the row with the dot command will not print. After you have entered .PAUSE in column A of a row, page breaks will cause a pause for you to insert the next page and give you an option to reprint a page or exit the print function. 6. Internal Rate of Return. This function provides an iterative approximation for the interest rate which will produce a zero for the Net Present Value function. The range for this function should include present cash flows as well as future cash flows. The approach to finding the zero is as follows, 14 loops thru the "Bisection Method", followed by up to 6 loops thru the "Modified-False Position Method". Usually the IRR function will converge to within 0.000001. In some instances, where the cash flows turn negative in a later period, there are multiple solutions, however IRR finds the closest one only. Your guess input to this function should be between 0.0 and 1.0. In some cases, the function will not converge, either the solution is too small or too large, in these cases the function will return ERR. Try adjusting you guess and recalculate.